package com.it.smartoldb.repository;

import com.it.smartoldb.entity.Staff;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 护理人员数据访问层
 */
@Repository
public interface StaffRepository extends JpaRepository<Staff, Long> {

    /**
     * 根据职位查找护理人员
     */
    List<Staff> findByPosition(String position);

    /**
     * 根据状态查找护理人员
     */
    List<Staff> findByStatus(Staff.StaffStatus status);

    /**
     * 根据姓名模糊查询
     */
    List<Staff> findByNameContaining(String name);

    /**
     * 根据身份证号查找护理人员
     */
    Optional<Staff> findByIdCard(String idCard);

    /**
     * 查询在职人员
     */
    List<Staff> findByStatusOrderByHireDateDesc(Staff.StaffStatus status);

    /**
     * 分页查询并支持多条件筛选
     */
    @Query("SELECT s FROM Staff s WHERE " +
           "(:name IS NULL OR s.name LIKE %:name%) AND " +
           "(:idCard IS NULL OR s.idCard LIKE %:idCard%) AND " +
           "(:position IS NULL OR s.position = :position) AND " +
           "(:status IS NULL OR s.status = :status)")
    Page<Staff> findStaffWithFilters(
            @Param("name") String name,
            @Param("idCard") String idCard,
            @Param("position") String position,
            @Param("status") Staff.StaffStatus status,
            Pageable pageable
    );

    /**
     * 统计各职位的人员数量
     */
    @Query("SELECT s.position, COUNT(s) FROM Staff s WHERE s.status = :status GROUP BY s.position")
    List<Object[]> countByPositionAndStatus(@Param("status") Staff.StaffStatus status);

    /**
     * 根据工作经验查找人员
     */
    List<Staff> findByWorkExperienceGreaterThanEqualAndStatusOrderByWorkExperienceDesc(
            Integer minExperience, Staff.StaffStatus status);

    /**
     * 根据技能关键字查找人员
     */
    @Query("SELECT s FROM Staff s WHERE s.skills LIKE %:skill% AND s.status = :status")
    List<Staff> findBySkillsContainingAndStatus(@Param("skill") String skill, 
                                               @Param("status") Staff.StaffStatus status);
} 